﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LTSMZ.life.Model;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
namespace LTSMZ.life.DAL
{
    public class OrdersDal
    {
        Qianli.DirSir.Common.SQLHelper sql = new Qianli.DirSir.Common.SQLHelper();
        /// <summary>
        /// 显示订单
        /// </summary> 
        /// <returns></returns>
        public List<OrdersComment> Ordersshow()
        {
            string str = "select c.Num,c.OrderID,c.OrderTime,c.Pname,c.State,d.Mname from(select a.OrderID,a.Num,a.OrderTime,a.State,b.Pname,a.Mid from Orders a  join Product b on a.Pid=b.Pid )c  join Member d on c.Mid=d.Mid";
            //SqlParameter
            //StringBuilder str = new StringBuilder();
            //str.Append("select c.Num,c.OrderID,c.OrderTime,c.Pname,c.State,d.Mname from(");
            //str.Append("select a.OrderID,a.Num,a.OrderTime,a.State,b.Pname,a.Mid from Orders a  join Product b on a.Pid=b.Pid)");
            //str.Append("c join Member d on c.Mid=d.Mid");
            //DataSet ds = sql.ExecDataSet(str.ToString());
            DataTable table = sql.ExecDataTable(str);
            List<OrdersComment> li = new List<OrdersComment>();
            foreach (DataRow i in table.Rows)
            {
                OrdersComment o = new OrdersComment();
                o.OrderID = Convert.ToInt32(i["OrderID"]);
                o.OrderTime = Convert.ToDateTime(i["OrderTime"]);
                o.Num = Convert.ToInt32(i["Num"]);
                o.State = Convert.ToInt32(i["State"]);
                o.Mname = i["Mname"].ToString();
                o.Pname = i["Pname"].ToString();
                li.Add(o);
            }
            return li;
            
        }
        /// <summary>
        /// 添加到订单
        /// </summary>
        /// <param name="o">订单集合</param>
        /// <param name="users">当前用户名编号</param>
        /// <param name="Mname">当前商品编号</param>
        /// <param name="Rid">当前地址编号</param>
        /// <returns></returns>
        //public int OrdersAdd(Orders o, int users, int Mname, int Rid)
        //{
            //string str = "insert into Orders(Num,OrderTime,State,Mid,Pid,Rid) values(@Num,@OrderTime,@State,@users,@Mname,@Rid)";
            //StringBuilder str = new StringBuilder();
            //str.Append("insert into Orders(");
            //str.Append("Num,OrderTime,State,Mid,Pid,Rid)");
            //str.Append("values(@Num,@OrderTime,@State,@users,@Mname,@Rid)");
            //SqlParameter[] param = new SqlParameter[]
            //{
            //    new SqlParameter("@Num",SqlDbType.Int,10),
            //    new SqlParameter("@OrderTime",SqlDbType.DateTime,30),
            //    new SqlParameter("@State",SqlDbType.Int,10),
            //    new SqlParameter("@users",SqlDbType.Int,10),
            //    new SqlParameter("@Mname",SqlDbType.Int,10),
            //    new SqlParameter("@Rid",SqlDbType.Int,10)
            //};
            //param[0].Value = o.Num;
            //param[1].Value = o.OrderTime;
            //param[2].Value = o.State;
            //param[3].Value = users;
            //param[4].Value = Mname;
            //param[5].Value = Rid;
            //string str = "insert into Orders(Num,OrderTime,State,Mid,Pid,Rid) values(" + o.Num + ",'" + o.OrderTime + "'," + o.State + "," + users + "," + Mname + "," + Rid + ")";
            //int i = 0;

            //i = sql.ExecNonquery(str);
            //return i;
        //}
        /// <summary>
        /// 删除订单
        /// </summary>
        /// <param name="id">订单Id</param>
        /// <returns></returns>
        public int OrdersDel(int id)
        {
            //string str = "update Orders set State=3 WHERE  OrderID=@Orderid";
            StringBuilder str = new System.Text.StringBuilder();
            str.Append("update Orders set State=3");
            str.Append(" WHERE OrderID=@Orderid");
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Orderid",SqlDbType.Int,10)
            };
            param[0].Value = id;
            //string str = "update Orders set State=3 WHERE  OrderID="+id+"";
            int i = 0;
            i = sql.ExecNonquery(str.ToString(), param);
            return i;
        }
        /// <summary>
        /// 订单查询
        /// </summary>
        /// <param name="OrderID">当前订单编号</param>
        /// <returns></returns>
        public List<OrdersComment> OrdersShowCha(int OrderID)
        {
            string str = "select c.Num,c.OrderID,c.OrderTime,c.Pname,c.State,d.Mname from(select a.OrderID,a.Num,a.OrderTime,a.State,b.Pname,a.Mid from Orders a  join Product b on a.Pid=b.Pid )c join Member d on c.Mid=d.Mid where c.OrderID=@orderid";
            //StringBuilder str = new System.Text.StringBuilder();
            //str.Append("select c.Num,c.OrderID,c.OrderTime,c.Pname,c.State,d.Mname from(");
            //str.Append("select a.OrderID,a.Num,a.OrderTime,a.State,b.Pname,a.Mid from Orders a join Product b on a.Pid=b.Pid)");
            //str.Append("c join Member d on c.Mid=d.Mid where c.OrderID=@orderid");
            //SqlParameter[] param = new SqlParameter[]
            //{
            //    new SqlParameter("@orderid",SqlDbType.Int,10)
            //};
            //string str = "select c.Num,c.OrderID,c.OrderTime,c.Pname,c.State,d.Mname from(select a.OrderID,a.Num,a.OrderTime,a.State,b.Pname,a.Mid from Orders a  join Product b on a.Pid=b.Pid )c  join Member d on c.Mid=d.Mid where c.OrderID="+OrderID+"";
            //StringBuilder str=new StringBuilder();
            //param[0].Value = OrderID;
            DataTable table = sql.ExecDataTable(str);
            List<OrdersComment> li = new List<OrdersComment>();
            foreach (DataRow i in table.Rows)
            {
                OrdersComment o = new OrdersComment();
                o.OrderID = Convert.ToInt32(i["OrderID"]);
                o.OrderTime = Convert.ToDateTime(i["OrderTime"]);
                o.Num = Convert.ToInt32(i["Num"]);
                o.State = Convert.ToInt32(i["State"]);
                o.Mname = i["Mname"].ToString();
                o.Pname = i["Pname"].ToString();
                li.Add(o);
            }
            return li;
        }
        /// <summary>
        /// 根据编号获取订单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        //public Orders GetOrderId(int id)
        //{
        //    string str = "select c.Num,c.OrderID,c.OrderTime,c.Pname,c.State,d.Mname from(select //a.OrderID,a.Num,a.OrderTime,a.State,b.Pname,a.Mid from Orders a  join Product b on a.Pid=b.Pid )c  //join Member d on c.Mid=d.Mid where OrderID=" + id + "";
        //    SqlDataReader dt = sql.ExecReader(str);
        //    Orders o = new Orders();
        //    while (dt.Read())
        //    {
        //        o.OrderID = Convert.ToInt32(dt["OrderID"]);
        //        o.Num = Convert.ToInt32(dt["Num"]);
        //        o.OrderTime = Convert.ToDateTime(dt["OrderTime"]);
        //        o.Pname = dt["Pname"].ToString();
        //        o.State = Convert.ToInt32(dt["State"]);
        //        o.Mname = dt["Mname"].ToString();
        //    }
        //    return o;
        //}
        /// <summary>
        /// 添加订单子表
        /// </summary>
        /// <param name="order"></param>
        /// <param name="xml"></param>
        /// <returns></returns>
        public int InsertOrderSon(Orders order,string xml)
        {
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@OrderTime",SqlDbType.DateTime),
                new SqlParameter("@state",SqlDbType.Int),
                new SqlParameter("@Mid",SqlDbType.Int),
                new SqlParameter("@Rid",SqlDbType.Int),
                new SqlParameter("@xml",SqlDbType.Xml),
            };

            parameter[0].Value=order.OrderTime;
            parameter[1].Value=order.State;
            parameter[2].Value=order.Mid;
            parameter[3].Value=order.Rid;
            parameter[4].Value=xml;

            int i=sql.ExecNonquery_Proc("Proc_InsertOrder", parameter);

            return i;
        }
        /// <summary>
        /// 订单显示
        /// </summary>
        /// <returns></returns>
        public List<Orders> ShowOrders(string name)
        {
            StringBuilder build = new StringBuilder();
            build.Append("select Orders.OrderID,Pname,RealName,OrderTime,OrderPrice,State from Orders join OrderSon on Orders.OrderID=OrderSon.OrderID join Product on OrderSon.Pid=Product.Pid join Member on Orders.Mid=Member.Mid where Mname=@name");

            SqlParameter parameter = new SqlParameter("@name",name);
            List<Orders> list = new List<Orders>();
            DataTable table=sql.ExecDataTable(build.ToString(), parameter);
            if(table.Rows.Count>0)
            {
                foreach (DataRow item in table.Rows)
	            {
	            	 Orders order = new Orders();
                    order.OrderID=Convert.ToInt32(item["OrderID"]);
                    order.Pname = item["Pname"].ToString();
                    order.RealName = item["RealName"].ToString() ;
                    order.OrderTime =Convert.ToDateTime( item["OrderTime"]).ToString("yyyy-MM-dd");
                    order.OrderPrice = Convert.ToDecimal(item["OrderPrice"].ToString());
                    order.State = Convert.ToInt32(item["State"]);
                    list.Add(order);
	            }
                
            }
            else
            {
                return null;
            }
            return list;
        }
    }
}
